<?php
class DailyModel extends BaseModel
{
    public static function analyze($date)
    {
        // insert new row
        $sql = 'INSERT INTO analyzed_daily (date) VALUES (:date)';
        $command = Yii::app()->db->createCommand($sql);
        $command->bindParam(':date', $date);
        $command->execute();

        // analyze from log
        $fields = require(Yii::getPathOfAlias('application.config.daily').'.php');
        foreach($fields as $field => $sqls)
        {
            // replace variables
            $searchs = array(':date', ':gender_male', ':gender_female', ':platform_web', ':platform_ios', ':platform_android');
            $replaces = array("'$date'", ENUM_GENDER_MALE, ENUM_GENDER_FEMALE, ENUM_PLATFORM_WEB, ENUM_PLATFORM_IOS, ENUM_PLATFORM_ANDROID);
            $sqls = str_replace($searchs, $replaces, $sqls);

            // run pre-sql
            $sqls = explode(';', $sqls);
            for($i = 0; $i < count($sqls)-1; $i++)
            {
                $command = Yii::app()->db->createCommand($sqls[$i]);
                $command->execute();
            }

            // run last sql
            $sql = $sqls[count($sqls)-1];
            $sql = "UPDATE analyzed_daily SET $field = ($sql) WHERE date = '$date'";
            $command = Yii::app()->db->createCommand($sql);
            $command->execute();
        }

        // analyze from live db
        $totalUser = LiveModel::getTotalUser($date);
        $totalMale = LiveModel::getTotalByGender($date, ENUM_GENDER_MALE);
        $totalFemale = LiveModel::getTotalByGender($date, ENUM_GENDER_FEMALE);
        $totalUserPerCountry = json_encode(LiveModel::getTotalUserPerCountry($date));
        $newUserPerCountry = json_encode(LiveModel::getNewUserPerCountry($date));
        $totalVerification = LiveModel::getTotalVerification();
        $totalIOSVerification = LiveModel::getTotalIOSVerification();
        $totalAndroidVerification = LiveModel::getTotalAndroidVerification();
        $totalWebVerification = $totalVerification - $totalIOSVerification - $totalAndroidVerification;
        $totalEnabledUser = LiveModel::getTotalEnabledUser();
        $totalUserGroupByAgeRange = LiveModel::getTotalUserGroupByAgeRange($date);
        $totalUserPlayedSnapshot = LiveModel::getTotalUserPlayedSnapshot($date);
        $averageSession = UserModel::calcAvgSession($date);
        $averageWebSession = UserModel::calcAvgSession($date, ENUM_PLATFORM_WEB);
        $averageIOSSession = UserModel::calcAvgSession($date, ENUM_PLATFORM_IOS);
        $averageAndroidSession = 0; //UserModel::calcAvgSession($date, ENUM_PLATFORM_ANDROID);
        $sql = "UPDATE analyzed_daily
                SET
                    total_user = $totalUser,
                    total_male = $totalMale,
                    total_female = $totalFemale,
                    total_user_per_country = '$totalUserPerCountry',
                    new_user_per_country = '$newUserPerCountry',
                    total_verification = $totalVerification,
                    total_verification_web = $totalWebVerification,
                    total_verification_ios = $totalIOSVerification,
                    total_verification_android = $totalAndroidVerification,
                    total_enabled_user = $totalEnabledUser,
                    total_user_under_20 = {$totalUserGroupByAgeRange['<20']},
                    total_user_20_25 = {$totalUserGroupByAgeRange['20-25']},
                    total_user_26_30 = {$totalUserGroupByAgeRange['26-30']},
                    total_user_upper_30 = {$totalUserGroupByAgeRange['>30']},
                    total_user_played_snapshot = $totalUserPlayedSnapshot,
                    average_session = $averageSession,
                    average_session_web = $averageWebSession,
                    average_session_ios = $averageIOSSession,
                    average_session_android = $averageAndroidSession
                WHERE date = '$date'";
        $command = Yii::app()->db->createCommand($sql);
        $command->execute();
    }
    
    public static function deleteByDate($date)
    {
        $sql = 'DELETE FROM analyzed_daily WHERE date = :date;';
        $command = Yii::app()->db->createCommand($sql);
        $command->bindParam(':date', $date);
        $command->execute();
    }
    
    public static function getByDate($date)
    {
        $sql = 'SELECT * FROM analyzed_daily WHERE date = :date';
        $command = Yii::app()->db->createCommand($sql);
        $command->bindParam(':date', $date);
        $row = $command->queryRow();
        $row['total_user_per_country'] = json_decode($row['total_user_per_country'], true);
        $row['new_user_per_country'] = json_decode($row['new_user_per_country'], true);
        return $row;
    }
    
    public static function getByPeriod($fromDate, $toDate)
    {
        $sql = 'SELECT * FROM analyzed_daily WHERE date BETWEEN :from_date AND :to_date';
        $command = Yii::app()->db->createCommand($sql);
        $command->bindParam(':from_date', $fromDate);
        $command->bindParam(':to_date', $toDate);
        $rows = $command->queryAll();
        foreach($rows as &$row)
        {
            $row['total_user_per_country'] = json_decode($row['total_user_per_country'], true);
            $row['new_user_per_country'] = json_decode($row['new_user_per_country'], true);
        }
        unset($row);
        return $rows;
    }

    public static function getAll()
    {
        $sql = 'SELECT * FROM analyzed_daily';
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        foreach($rows as &$row)
        {
            $row['total_user_per_country'] = json_decode($row['total_user_per_country'], true);
            $row['new_user_per_country'] = json_decode($row['new_user_per_country'], true);
        }
        unset($row);
        return $rows;
    }
}
